﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using SPC.Properties;
using SCPutils;

namespace SPC.Classes
{
    class PenaltyEvents
    {
        public static string PENALTY_TABLE = "Penalties";

        public PenaltyEvents() { }
                
        public List<Penalty> GetAllPenalties4Person(int intPersonID)
        {
            return GetAllPenalties4Person(intPersonID, 0);
        }

        public List<Penalty> GetAllPenalties4Person(int intPersonID, int intTimestampVersion)
        {
            try
            {
                SQLiteDatabase db = new SQLiteDatabase();
                DataTable penalty = new DataTable();
                String query = "select id, person_id, version, " + Properties.Settings.Default.PENALTY_ATTRIB_DATE
                    + ", " + Properties.Settings.Default.PENALTY_ATTRIB_RATE
                    + ", " + Properties.Settings.Default.PENALTY_ATTRIB_DUE_DATE
                    + ", " + Properties.Settings.Default.PENALTY_ATTRIB_PERCENT
                    + ", " + Properties.Settings.Default.PENALTY_ATTRIB_DAYS_COUNT
                    + ", " + Properties.Settings.Default.PENALTY_ATTRIB_PENALTY_PER_DAY
                    + ", " + Properties.Settings.Default.PENALTY_ATTRIB_PENALTY_VALUE
                    + ", " + Properties.Settings.Default.PENALTY_ATTRIB_TOTAL_PENALTY;
                query += " from " + PENALTY_TABLE;
                if(intPersonID > 0) query += " where person_id = " + intPersonID ;
                if(intTimestampVersion > 0) query += " and version=" + intTimestampVersion;
                query += " Order by " + Properties.Settings.Default.PENALTY_ATTRIB_DATE + ";";
                //MessageBox.Show(query);
                penalty = db.GetDataTable(query);

                List<Penalty> listPenalties = new List<Penalty>();
                // Or looped through for some other reason
                foreach (DataRow r in penalty.Rows)
                {
                    Penalty objPenalty = new Penalty();
                    objPenalty.setID(r["id"].ToString());
                    objPenalty.setPersonID(r["person_id"].ToString());
                    objPenalty.setVersion(r["version"].ToString());
                    
                    objPenalty.setDate(r[Properties.Settings.Default.PENALTY_ATTRIB_DATE].ToString());
                    objPenalty.setRate(r[Properties.Settings.Default.PENALTY_ATTRIB_RATE].ToString());
                    objPenalty.setDueDate(r[Properties.Settings.Default.PENALTY_ATTRIB_DUE_DATE].ToString());
                    objPenalty.setPercent(r[Properties.Settings.Default.PENALTY_ATTRIB_PERCENT].ToString());
                    objPenalty.setDaysCount(r[Properties.Settings.Default.PENALTY_ATTRIB_DAYS_COUNT].ToString());
                    objPenalty.setPenaltyPerDay(r[Properties.Settings.Default.PENALTY_ATTRIB_PENALTY_PER_DAY].ToString());
                    objPenalty.setPenaltyValue(r[Properties.Settings.Default.PENALTY_ATTRIB_PENALTY_VALUE].ToString());
                    objPenalty.setTotalPenalty(r[Properties.Settings.Default.PENALTY_ATTRIB_TOTAL_PENALTY].ToString());

                    listPenalties.Add(objPenalty);
                }

                return listPenalties;
            }
            catch (Exception fail)
            {
                String error = "The following error has occurred:\n\n";
                error += fail.Message.ToString() + "\r\n";
                error += fail.StackTrace.ToString() + "\r\n";
                MessageBox.Show(error);
                return null;
            }
        }

        public Penalty GetPenalty(string strPenaltyID)
        {
            try
            {
                SQLiteDatabase db = new SQLiteDatabase();
                DataTable penalty = new DataTable();
                String query = "select id, person_id, version, " + Properties.Settings.Default.PENALTY_ATTRIB_DATE
                    + ", " + Properties.Settings.Default.PENALTY_ATTRIB_RATE
                    + ", " + Properties.Settings.Default.PENALTY_ATTRIB_DUE_DATE
                    + ", " + Properties.Settings.Default.PENALTY_ATTRIB_PERCENT
                    + ", " + Properties.Settings.Default.PENALTY_ATTRIB_DAYS_COUNT
                    + ", " + Properties.Settings.Default.PENALTY_ATTRIB_PENALTY_PER_DAY
                    + ", " + Properties.Settings.Default.PENALTY_ATTRIB_PENALTY_VALUE
                    + ", " + Properties.Settings.Default.PENALTY_ATTRIB_TOTAL_PENALTY;
                query += " from " + PENALTY_TABLE;
                query += " where id = " + strPenaltyID + ";";
                
                penalty = db.GetDataTable(query);

                Penalty objPenalty = new Penalty();
                // Or looped through for some other reason
                foreach (DataRow r in penalty.Rows)
                {
                    objPenalty.setID(r["id"].ToString());
                    objPenalty.setPersonID(r["person_id"].ToString());
                    objPenalty.setVersion(r["version"].ToString());

                    objPenalty.setDate(r[Properties.Settings.Default.PENALTY_ATTRIB_DATE].ToString());
                    objPenalty.setRate(r[Properties.Settings.Default.PENALTY_ATTRIB_RATE].ToString());
                    objPenalty.setDueDate(r[Properties.Settings.Default.PENALTY_ATTRIB_DUE_DATE].ToString());
                    objPenalty.setPercent(r[Properties.Settings.Default.PENALTY_ATTRIB_PERCENT].ToString());
                    objPenalty.setDaysCount(r[Properties.Settings.Default.PENALTY_ATTRIB_DAYS_COUNT].ToString());
                    objPenalty.setPenaltyPerDay(r[Properties.Settings.Default.PENALTY_ATTRIB_PENALTY_PER_DAY].ToString());
                    objPenalty.setPenaltyValue(r[Properties.Settings.Default.PENALTY_ATTRIB_PENALTY_VALUE].ToString());
                    objPenalty.setTotalPenalty(r[Properties.Settings.Default.PENALTY_ATTRIB_TOTAL_PENALTY].ToString());

                    break;
                }

                return objPenalty;
            }
            catch (Exception fail)
            {
                String error = "The following error has occurred:\n\n";
                error += fail.Message.ToString() + "\r\n";
                error += fail.StackTrace.ToString() + "\r\n";
                MessageBox.Show(error);
                return null;
            }
        }

        public void GetAllPenalties4Person2GridView(int intPersonID, int intTimestampVersion, DataGridView DGV, List<Label> List_L_Sums)
        {
            List<Penalty> listPenaltiesVersion = new List<Penalty>();
            PenaltyEvents eventPenalties = new PenaltyEvents();
            listPenaltiesVersion = eventPenalties.GetAllPenalties4Person(intPersonID, intTimestampVersion);

            System.Configuration.SettingsPropertyCollection settings = Properties.Settings.Default.Properties;
            int intAttributesCounter = 0;
            foreach (System.Configuration.SettingsProperty property in settings)
            {
                if(property.Name.Contains("PENALTY_ATTRIB")) intAttributesCounter++;
            }

            DGV.ColumnCount = intAttributesCounter + 1; //+1 from ID
            DGV.Rows.Clear();

            double dbTotalSum = 0;
            double dbTotalRate = 0;
            double dbTotalPenalty = 0;
            int intMonthsLimit4Total = Properties.Settings.Default.PRINT_PENALTY_ROW_LIMIT;
            int intCountPenalties = listPenaltiesVersion.Count();
            int intStartSummingIndex = 0;
            if (intCountPenalties > intMonthsLimit4Total)
            {
                intStartSummingIndex = intCountPenalties - intMonthsLimit4Total;
            }
            //int counter = 0;
            string strStartDate = "";
            string strEndDate = "";
            foreach (Penalty objPenalty in listPenaltiesVersion)
            {
                DGV.Rows.Add(
                    objPenalty.getID()
                    ,Utils.Unix.DateTimeFromUnixTimestampSeconds(long.Parse(objPenalty.getDate())).ToString("MM/yyyy")
                    , objPenalty.getRate()
                    , Utils.Unix.DateTimeFromUnixTimestampSeconds(long.Parse(objPenalty.getDueDate())).ToString("dd/MM/yyyy")
                    , objPenalty.getPercent()
                    , objPenalty.getDaysCount()
                    , objPenalty.getPenaltyPerDay()
                    , objPenalty.getPenaltyValue()
                    , objPenalty.getTotalPenalty());

                if (listPenaltiesVersion.IndexOf(objPenalty) >= intStartSummingIndex)
                {
                    dbTotalSum += double.Parse(objPenalty.getTotalPenalty());
                    dbTotalRate += double.Parse(objPenalty.getRate());
                    dbTotalPenalty += double.Parse(objPenalty.getPenaltyValue());
                    //counter++;
                    if (listPenaltiesVersion.IndexOf(objPenalty) == intStartSummingIndex) strStartDate = objPenalty.getDate();
                    if (listPenaltiesVersion.IndexOf(objPenalty) == listPenaltiesVersion.Count-1) strEndDate = objPenalty.getDate();
                }
            }
            //MessageBox.Show(counter.ToString());
            List_L_Sums[0].Text = Math.Round(dbTotalSum, 2).ToString();
            List_L_Sums[1].Text = Math.Round(dbTotalRate, 2).ToString();
            List_L_Sums[2].Text = Math.Round(dbTotalPenalty, 2).ToString();
            List_L_Sums[3].Text = String.Format(Resources.L_Penalties_Details, listPenaltiesVersion.Count.ToString(), Properties.Settings.Default.PRINT_PENALTY_ROW_LIMIT);
            List_L_Sums[4].Text = String.Format(Resources.L_Penalties_Details2, 
                Utils.Unix.DateTimeFromUnixTimestampSeconds(long.Parse( strStartDate)).ToString("MM/yyyy"),
                Utils.Unix.DateTimeFromUnixTimestampSeconds(long.Parse(strEndDate)).ToString("MM/yyyy"));
            //DGV.Col

            DGV.Columns[0].HeaderText = "ID";
            DGV.Columns[0].Width = 0;
            DGV.Columns[0].Name = "ID";
            //DGV.Columns[0].Visible = false;

            DGV.Columns[1].HeaderText = Resources.TXT_PENALTY_DATE;
            DGV.Columns[1].Name = Resources.TXT_PENALTY_DATE;
            DGV.Columns[1].Width = 60;

            DGV.Columns[2].HeaderText = Resources.TXT_PENALTY_RATE;
            DGV.Columns[2].Name = Resources.TXT_PENALTY_RATE;

            DGV.Columns[3].HeaderText = Resources.TXT_PENALTY_DUE_DATE;
            DGV.Columns[3].Name = Resources.TXT_PENALTY_DUE_DATE;

            DGV.Columns[4].HeaderText = Resources.TXT_PENALTY_PERCENT;
            DGV.Columns[4].Name = Resources.TXT_PENALTY_PERCENT;

            DGV.Columns[5].HeaderText = Resources.TXT_PENALTY_DAYS_COUNT;
            DGV.Columns[5].Name = Resources.TXT_PENALTY_DAYS_COUNT;

            DGV.Columns[6].HeaderText = Resources.TXT_PENALTY_PER_DAY;
            DGV.Columns[6].Name = Resources.TXT_PENALTY_PER_DAY;

            DGV.Columns[7].HeaderText = Resources.TXT_PENALTY_VALUE;
            DGV.Columns[7].Name = Resources.TXT_PENALTY_VALUE;

            DGV.Columns[8].HeaderText = Resources.TXT_PENALTY_TOTAL;
            DGV.Columns[8].Name = Resources.TXT_PENALTY_TOTAL;

            //Autoscroll to the bottom of the DGV.
            if (DGV.Rows.Count > 0)
            {
                DGV.CurrentCell = null;
                DGV.CurrentCell = DGV.Rows[DGV.Rows.Count - 1].Cells[0];
            }  

        }

        public void Insert(Penalty objPenalty)
        {

            SQLiteDatabase db = new SQLiteDatabase();
            Dictionary<String, String> data = new Dictionary<String, String>();
            data.Add("version", objPenalty.getVersion());
            data.Add("person_id", objPenalty.getPersonID());
            data.Add(Properties.Settings.Default.PENALTY_ATTRIB_DATE, objPenalty.getDate());
            data.Add(Properties.Settings.Default.PENALTY_ATTRIB_RATE, objPenalty.getRate());
            data.Add(Properties.Settings.Default.PENALTY_ATTRIB_DUE_DATE, objPenalty.getDueDate());
            data.Add(Properties.Settings.Default.PENALTY_ATTRIB_PERCENT, objPenalty.getPercent());
            data.Add(Properties.Settings.Default.PENALTY_ATTRIB_DAYS_COUNT, objPenalty.getDaysCount());
            data.Add(Properties.Settings.Default.PENALTY_ATTRIB_PENALTY_PER_DAY, objPenalty.getPenaltyPerDay());
            data.Add(Properties.Settings.Default.PENALTY_ATTRIB_PENALTY_VALUE, objPenalty.getPenaltyValue());
            data.Add(Properties.Settings.Default.PENALTY_ATTRIB_TOTAL_PENALTY, objPenalty.getTotalPenalty());
            try
            {
                db.Insert(PENALTY_TABLE, data);
            }
            catch (Exception crap)
            {
                MessageBox.Show(crap.Message);
            }
        }

        public void Update(Penalty objPenalty)
        {
            SQLiteDatabase db = new SQLiteDatabase();
            Dictionary<String, String> data = new Dictionary<String, String>();
            data.Add("version", objPenalty.getVersion());
            data.Add("person_id", objPenalty.getPersonID());
            data.Add(Properties.Settings.Default.PENALTY_ATTRIB_DATE, objPenalty.getDate());
            data.Add(Properties.Settings.Default.PENALTY_ATTRIB_RATE, objPenalty.getRate());
            data.Add(Properties.Settings.Default.PENALTY_ATTRIB_DUE_DATE, objPenalty.getDueDate());
            data.Add(Properties.Settings.Default.PENALTY_ATTRIB_PERCENT, objPenalty.getPercent());
            data.Add(Properties.Settings.Default.PENALTY_ATTRIB_DAYS_COUNT, objPenalty.getDaysCount());
            data.Add(Properties.Settings.Default.PENALTY_ATTRIB_PENALTY_PER_DAY, objPenalty.getPenaltyPerDay());
            data.Add(Properties.Settings.Default.PENALTY_ATTRIB_PENALTY_VALUE, objPenalty.getPenaltyValue());
            data.Add(Properties.Settings.Default.PENALTY_ATTRIB_TOTAL_PENALTY, objPenalty.getTotalPenalty());
            try
            {
                db.Update(PENALTY_TABLE, data, "id = " + objPenalty.getID());
            }
            catch (Exception crap)
            {
                MessageBox.Show(crap.Message);
            }
        }

        public void Delete(String strPenaltyID)
        {
            SQLiteDatabase db = new SQLiteDatabase();
            db.Delete(PENALTY_TABLE, "id = " + strPenaltyID);
            //db.Delete(PenaltyEvents.PENALTY_TABLE, "person_id = " + strPersonID);
        }
    }
}
